USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0004_CARGA_TABLA]    Script Date: 11/25/2011 11:54:05 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_M0004_CARGA_TABLA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_M0004_CARGA_TABLA]
GO

USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0004_CARGA_TABLA]    Script Date: 11/25/2011 11:54:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[WASP_M0004_CARGA_TABLA]

AS
BEGIN
	SELECT SCSUC , SCCTA , SCOPER 
	INTO #csaldos
	FROM 
		FSD011 A (NOLOCK)
	WHERE
		A.SCMOD IN (30,32,46) AND
		A.SCSTAT NOT IN (98,99) AND 
		A.SCSDO <> 0 AND SCSBOP < 100
	GROUP BY A.SCSUC , A.SCCTA , A.SCOPER
	ORDER BY A.SCSUC , A.SCCTA , A.SCOPER

	select SCSUC , SCCTA , SCOPER, max(aoimp) aoimp, max(aotasa) aotasa
	into  #temp
	from 
		#csaldos,
		fsd010 a (NOLOCK)
	where  a.aocta = sccta and 
		   a.aooper = scoper and
		   a.aostat <> 99 and
		   a.aosuc = scsuc and
           aosbop < 100 
	group by SCSUC , SCCTA , SCOPER

	select a.aosuc, a.aocta, a.aooper as CRCREDITO,
	rtrim(ltrim(c.pfndoc) )as RUTTRABAJA,
	cast(convert(varchar,c.Pffnac,112) as decimal(8,0)) as FNACITRABA,
	SEXOTRABAJ = 
		case
			when c.Pfcant = 'M'  then 0
			else 1
		end,
	CAST(cast(#temp.aoimp as numeric)AS VARCHAR(9))+'.0' AS CRMONTO,
	null as CRCUOTAS, 
	cast(convert(float,round(max(a.aotasa),4)) as varchar)  as CRTASA,
	--CONVERT(VARCHAR(8),convert(float,round(max(a.aotasa)/100,4))) as CRTASA,
	--(cast((max(a.aotasa) * 1) as decimal(4,4)) as tasa,
	--convert (numeric(4,4),max(a.aotasa)) as   tasa,
	--convert(decimal(4,4),convert( float,substring(cast(max(a.aotasa) as varchar),1,len(cast(max(a.aotasa) as varchar))-1))) as tasa,
	--convert(varchar,cast(max(a.aotasa) as  decimal(4,4))) as CRTASA,
       -- '000' + substring(convert(char , max(a.aotasa)),1,1)  + substring(convert(char , max(a.aotasa)),3,2) +  '00'  as CRTASA,
        0 as CRCLASE,
        cast(convert(varchar,min(a.aofval),112) as decimal(8,0))  as CRFECAPROB,
	TIPO_TRABAJADOR = 
	case
		when (d.ctccli = 3)then 'T'
		when (d.ctccli = 4)then 'P'
		when (d.ctccli = 5)then 'A'
	end
	
	into  dbo.as_creditos_vigentes_temp
	from 
		fsd010 a (nolock), 
		fsr008 b (nolock), 
		fsd002 c (nolock),
		fsd008 d (nolock), 
		#temp 
	where 
		#temp.scsuc = a.aosuc and 
        #temp.sccta = a.aocta and 
        #temp.scoper = a.aooper and 
        #temp.aoimp = a.aoimp and 
		a.aocta = b.ctnro and
		b.Pendoc = c.Pfndoc and
		a.aocta = d.ctnro  
	group by #temp.aoimp, d.Ctccli, a.Aosuc,  a.Aooper, c.Pfndoc, c.Pffnac, c.Pfcant, a.Aocta
END

GO


